from sqlalchemy import Column, Integer, String, text
from sqlalchemy.exc import PendingRollbackError
from sqlalchemy.ext.declarative import declarative_base
from inits import DB, dbSession

#定义基类
Base = declarative_base()

class sk_shop_info(Base):
    __tablename__ = 'sk_shop_info'  # 数据库中表的名字
    id = Column(Integer, primary_key=True)
    name = Column(String(255))
    account = Column(String(255))
    password = Column(String(255))
    is_abnormal = Column(String(1))
    abnormal_msg = Column(String(255))
    abnormal_type = Column(Integer)
    status = Column(String(1))
    user_name = Column(String(2000))
    platform = Column(Integer)
    dept_ids = Column(String(500))
    dept_names = Column(String(500))
    business_manager_ids = Column(String(500))
    code_user_id = Column(Integer)
    phonenumber = Column(String(100))
    ali_source = Column(Integer)

    # 获取店铺数据
    def find_shop_list(self, platform):
        try:
            sql = text("""
                          SELECT a.*, b.phonenumber as callphone
                              FROM sk_shop_info as a
                          LEFT JOIN sys_user as b
                              ON a.code_user_id = b.user_id
                          WHERE a.status = 0 AND a.platform = :platform
                      """)
            res = dbSession.execute(sql, {"platform": platform}).fetchall()
        except PendingRollbackError:
            dbSession.rollback()
        finally:
            dbSession.close()
        return res

    # 修改店铺数据
    def update_abnormal_shop_id(self, shop_id, msg):
        try:
            sql = text("""
                  UPDATE sk_shop_info
                      SET is_abnormal = 1, abnormal_msg = :msg, abnormal_type = 1
                  WHERE id = :shop_id
              """)
            dbSession.execute(sql, {"msg": msg, "shop_id": shop_id})
            dbSession.commit()
        except PendingRollbackError:
            dbSession.rollback()
        finally:
            dbSession.close()

    def update_shop_abnormal(self, shop_id):
        sql = text("""
            UPDATE sk_shop_info SET is_abnormal = 0, abnormal_msg = "", abnormal_type = 0 WHERE id = :shopId
        """)

        try:
            dbSession.execute(sql, {"shopId": shop_id})
            dbSession.commit()
        except PendingRollbackError:
            dbSession.rollback()
        finally:
            dbSession.close()


